from workerSpace import getconnection, app
from flask import render_template, request, redirect, url_for, flash, jsonify
import requests, json
from datetime import datetime, timezone

@app.route('/pushMessage', methods=['GET', 'POST'])
def pushMessage():
    try:
        if request.method == 'GET':
            # 处理 GET 请求
            signature = request.args.get('signature', default=None)
            echostr = request.args.get('echostr', default=None)
            timestamp = request.args.get('timestamp', default=None)
            nonce = request.args.get('nonce', default=None)
            # 校验通过，返回 echostr
            return echostr
        elif request.method == 'POST':
            # 处理 POST 请求
            # 这里可以添加处理 POST 请求的代码
            return "POST 请求处理"

        return "未知请求方法", 405
    except Exception as e:
        return str(e), 500

@app.route('/saveOpenId', methods=['GET'])
def saveOpenId():
    try:
        # openId = request.args.get('openId', default=None)
        code = request.args.get('code', default=None)
        print("code:" + code)
        url= f"https://api.weixin.qq.com/sns/jscode2session?appid=wx45daf5049080ec1a&secret=5a467f6c4613172aca8c5bba7d5e77fd&js_code={code}&grant_type=authorization_code"
        res = requests.get(url).json()
        openId = res.get("openid")
        now = datetime.now(timezone.utc)
        fetchall = executeSql(f"REPLACE INTO `push_open_id` (`open_id`, `team_id`,`create_time`) VALUES ( '{openId}', '1', '{now}')")
        return jsonify(fetchall)
    except Exception as e:
        return jsonify(e)


@app.route('/getOpenId', methods=['GET'])
def getOpenId():
    try:
        code = request.args.get('code', default=None)
        print(code)
        url= f"https://api.weixin.qq.com/sns/jscode2session?appid=wx45daf5049080ec1a&secret=5a467f6c4613172aca8c5bba7d5e77fd&js_code={code}&grant_type=authorization_code"
        res = requests.get(url).json()
        print(res)
        openId = res.get("openid")
        return jsonify(openId), 200
    except Exception as e:
        print(e)
        return e
    
@app.route('/translation', methods=['GET'])
def translation():
    try:
        data = executeSql("select * from learning_english ORDER BY RAND() LIMIT 1")[0]
        return jsonify(data), 200
    except Exception as e:
        print(e)
        return e

API_URL = "https://api.weixin.qq.com/cgi-bin/message/subscribe/send";

def send_subscribe_message(access_token, openid, template_id, page, data):
    url = f"{API_URL}?access_token={access_token}"
    headers = {
        "Content-Type": "application/json;charset=UTF-8"
    }
    body = build_request_body(openid, template_id, page, data)
    print(body)
    try:
        response = requests.post(url, headers=headers, data = body)
        response_code = response.status_code

        if response_code == 200:
            print("发送订阅消息成功")
            print(response.text)
        else:
            print("发送订阅消息失败")
            print("响应码：", response_code)
            print(response.text)

    except Exception as e:
        print("请求异常")
        print(str(e))

def build_request_body(openid, template_id, page, data):
    request_body = {
        "touser": openid,
        "template_id": template_id,
        "page": page,
        "data": data
    }
    # 将字典转换为 JSON 字符串
    return json.dumps(request_body)

def refreshAccessToken():
    try:
        url = f"https://api.weixin.qq.com/cgi-bin/token?grant_type=client_credential&appid=wx45daf5049080ec1a&secret=5a467f6c4613172aca8c5bba7d5e77fd"
        response = requests.get(url)
        if response.status_code == 200:
            response_data = response.json()
            access_token = response_data.get("access_token")
            print(access_token)
            return access_token
        else:
            print("获取AccessToken失败")
            print("响应码：", response.status_code)
            print("响应内容：", response.text)
            return None
    except Exception as e:
        print("请求异常" + str(e))
        return str(e), 500
    
@app.route('/sendMsg', methods=['GET', 'POST'])
def sendMsg():
    try:
        # sendMsgByTemplate("7D0reKnOahBzBPA7425eMWTHFl4DXPJW_362m84_nMs")
        templateId = request.args.get('templateId', default="7D0reKnOahBzBPA7425eMWTHFl4DXPJW_362m84_nMs")
        sendMsgByTemplate(templateId)
        return jsonify({"status": "success"}), 200
    except Exception as e:
        print(e)
        return str(e), 500

def sendMsgByTemplate(templateId):
    try:
        # 请求微信接口获取accessToken
        access_token = refreshAccessToken()
        data = executeSql(f"SELECT * FROM push_message where template_id = '{templateId}' order by create_time desc limit 1")[0]
        print(data)
        msg = json.loads(data['data'])
        template_id = data['template_id']
        page = data['page']

        userList = executeSql("SELECT * FROM push_open_id where flag = 1")
        for user in userList:
            send_subscribe_message(access_token, user['open_id'], template_id, page, msg)
        
        return jsonify({"status": "success"}), 200
    except Exception as e:
        print(e)
        return str(e), 500

@app.route('/test')
def index():
    try:
        with getconnection().cursor() as cursor:
            sql = "SELECT * FROM `snack_info`"
            cols = ['id', 'name']
            cursor.execute(sql)
            result = cursor.fetchall()
            cursor.close()
            return jsonify(result)
    except Exception as e:
        cursor.close()
        return str(e), 500

@app.route('/')
def hello_world():
   return 'Hello World'

# 搜索
@app.route('/search')
def search():
    keyword = request.args.get('keyword').strip()
    try:
        with getconnection().cursor() as cursor:
            sql = "SELECT * FROM `tb_user` where name like concat('%%',%s,'%%')"
            cols = ['id', 'name', 'age','gender','phone']
            cursor.execute(sql,(keyword))
            result = cursor.fetchall()
            # print(result)
            cursor.close()
            return jsonify(result)
    except Exception as e:
        cursor.close()
        return str(e), 500
    

@app.route('/add', methods=['POST'])
def add():
    name = request.form['name'].strip()
    age = request.form['age'].strip()
    gender = request.form['gender'].strip()
    phone = request.form['phone'].strip()
    try:
        with getconnection().cursor() as cursor:
            sql = "INSERT INTO `tb_user` (`name`, `age`,`gender`,`phone`) VALUES (%s, %s,%s,%s)"
            cursor.execute(sql, (name, age,gender,phone))
            cursor.close()
            return "ok"
    except Exception as e:
        cursor.close()
        return str(e), 500

@app.route('/edit',methods=['POST'])
def edit():
    id = request.form['id'].strip()
    name = request.form['name'].strip()
    age = request.form['age'].strip()
    phone = request.form['phone'].strip()
    gender = request.form['gender'].strip()
    try:
        with getconnection().cursor() as cursor:
            sql = "update `tb_user` set name=%s,age=%s,gender=%s,phone=%s where id=%s"
            cursor.execute(sql, (name, age,gender,phone,id))
            cursor.close()
            return "ok"
    except Exception as e:
        cursor.close()
        return str(e), 500

@app.route('/remove/<int:id>/')
def remove(id):
    try:
        with getconnection().cursor() as cursor:
            sql = "delete from `tb_user` where id=%s"
            cursor.execute(sql, (id))
            cursor.close()
            return "ok"
    except Exception as e:
        cursor.close()
        return str(e), 500

def executeSql(sql):
    try:
        print(sql)
        connection = getconnection();
        with connection.cursor() as cursor:
            cursor.execute(sql)
            cursor.close()
            connection.commit()
            return cursor.fetchall()
    except Exception as e:
        cursor.close()
        print(e)
        return str(e), 500